﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using GH.DBUtility;
using System.Collections.Generic;//请先添加引用
namespace GH.DAL
{
    /// <summary>
    /// 数据访问类互助员工统计表。
    /// </summary>
    public class MutualMember
    {
        public MutualMember()
        { }
        #region  成员方法

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int 单位, int No)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from 互助员工统计表");
            strSql.Append(" where 单位=" + 单位 + " and No=" + No + " ");
            return DbHelperSQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(GH.Model.互助员工统计表 model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.No != null)
            {
                strSql1.Append("No,");
                strSql2.Append("" + model.No + ",");
            }
            if (model.姓名 != null)
            {
                strSql1.Append("姓名,");
                strSql2.Append("'" + model.姓名 + "',");
            }
            if (model.性别 != null)
            {
                strSql1.Append("性别,");
                strSql2.Append("'" + model.性别 + "',");
            }
            if (model.投保时间 != null)
            {
                strSql1.Append("投保时间,");
                strSql2.Append("'" + model.投保时间 + "',");
            }
            if (model.投保金额 != null)
            {
                strSql1.Append("投保金额,");
                strSql2.Append("" + model.投保金额 + ",");
            }
            if (model.补助时间1 != null)
            {
                strSql1.Append("补助时间1,");
                strSql2.Append("'" + model.补助时间1 + "',");
            }
            if (model.金额1 != null)
            {
                strSql1.Append("金额1,");
                strSql2.Append("" + model.金额1 + ",");
            }
            if (model.补助时间2 != null)
            {
                strSql1.Append("补助时间2,");
                strSql2.Append("'" + model.补助时间2 + "',");
            }
            if (model.金额2 != null)
            {
                strSql1.Append("金额2,");
                strSql2.Append("" + model.金额2 + ",");
            }
            if (model.补助时间3 != null)
            {
                strSql1.Append("补助时间3,");
                strSql2.Append("'" + model.补助时间3 + "',");
            }
            if (model.金额3 != null)
            {
                strSql1.Append("金额3,");
                strSql2.Append("" + model.金额3 + ",");
            }
            if (model.补助时间4 != null)
            {
                strSql1.Append("补助时间4,");
                strSql2.Append("'" + model.补助时间4 + "',");
            }
            if (model.金额4 != null)
            {
                strSql1.Append("金额4,");
                strSql2.Append("" + model.金额4 + ",");
            }
            if (model.补助时间5 != null)
            {
                strSql1.Append("补助时间5,");
                strSql2.Append("'" + model.补助时间5 + "',");
            }
            if (model.金额5 != null)
            {
                strSql1.Append("金额5,");
                strSql2.Append("" + model.金额5 + ",");
            }
            if (model.备注 != null)
            {
                strSql1.Append("备注,");
                strSql2.Append("'" + model.备注 + "',");
            }
            strSql.Append("insert into 互助员工统计表(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(GH.Model.互助员工统计表 model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update 互助员工统计表 set ");
            if (model.姓名 != null)
            {
                strSql.Append("姓名='" + model.姓名 + "',");
            }
            if (model.性别 != null)
            {
                strSql.Append("性别='" + model.性别 + "',");
            }
            if (model.投保时间 != null)
            {
                strSql.Append("投保时间='" + model.投保时间 + "',");
            }
            if (model.投保金额 != null)
            {
                strSql.Append("投保金额=" + model.投保金额 + ",");
            }
            if (model.补助时间1 != null)
            {
                strSql.Append("补助时间1='" + model.补助时间1 + "',");
            }
            if (model.金额1 != null)
            {
                strSql.Append("金额1=" + model.金额1 + ",");
            }
            if (model.补助时间2 != null)
            {
                strSql.Append("补助时间2='" + model.补助时间2 + "',");
            }
            if (model.金额2 != null)
            {
                strSql.Append("金额2=" + model.金额2 + ",");
            }
            if (model.补助时间3 != null)
            {
                strSql.Append("补助时间3='" + model.补助时间3 + "',");
            }
            if (model.金额3 != null)
            {
                strSql.Append("金额3=" + model.金额3 + ",");
            }
            if (model.补助时间4 != null)
            {
                strSql.Append("补助时间4='" + model.补助时间4 + "',");
            }
            if (model.金额4 != null)
            {
                strSql.Append("金额4=" + model.金额4 + ",");
            }
            if (model.补助时间5 != null)
            {
                strSql.Append("补助时间5='" + model.补助时间5 + "',");
            }
            if (model.金额5 != null)
            {
                strSql.Append("金额5=" + model.金额5 + ",");
            }
            if (model.备注 != null)
            {
                strSql.Append("备注='" + model.备注 + "',");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where 单位=" + model.单位 + " and No=" + model.No + " ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int 单位, int No)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from 互助员工统计表 ");
            strSql.Append(" where 单位=" + 单位 + " and No=" + No + " ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int No)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from 互助员工统计表 ");
            strSql.Append(" where  No=" + No + " ");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public GH.Model.互助员工统计表 GetModel(int 单位, int No)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" 单位,No,姓名,性别,投保时间,投保金额,补助时间1,金额1,补助时间2,金额2,补助时间3,金额3,补助时间4,金额4,补助时间5,金额5,备注 ");
            strSql.Append(" from 互助员工统计表 ");
            strSql.Append(" where 单位=" + 单位 + " and No=" + No + " ");
            GH.Model.互助员工统计表 model = new GH.Model.互助员工统计表();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["单位"].ToString() != "")
                {
                    model.单位 = int.Parse(ds.Tables[0].Rows[0]["单位"].ToString());
                }
                if (ds.Tables[0].Rows[0]["No"].ToString() != "")
                {
                    model.No = int.Parse(ds.Tables[0].Rows[0]["No"].ToString());
                }
                model.姓名 = ds.Tables[0].Rows[0]["姓名"].ToString();
                model.性别 = ds.Tables[0].Rows[0]["性别"].ToString();
                if (ds.Tables[0].Rows[0]["投保时间"].ToString() != "")
                {
                    model.投保时间 = DateTime.Parse(ds.Tables[0].Rows[0]["投保时间"].ToString());
                }
                if (ds.Tables[0].Rows[0]["投保金额"].ToString() != "")
                {
                    model.投保金额 = int.Parse(ds.Tables[0].Rows[0]["投保金额"].ToString());
                }
                if (ds.Tables[0].Rows[0]["补助时间1"].ToString() != "")
                {
                    model.补助时间1 = DateTime.Parse(ds.Tables[0].Rows[0]["补助时间1"].ToString());
                }
                if (ds.Tables[0].Rows[0]["金额1"].ToString() != "")
                {
                    model.金额1 = int.Parse(ds.Tables[0].Rows[0]["金额1"].ToString());
                }
                if (ds.Tables[0].Rows[0]["补助时间2"].ToString() != "")
                {
                    model.补助时间2 = DateTime.Parse(ds.Tables[0].Rows[0]["补助时间2"].ToString());
                }
                if (ds.Tables[0].Rows[0]["金额2"].ToString() != "")
                {
                    model.金额2 = int.Parse(ds.Tables[0].Rows[0]["金额2"].ToString());
                }
                if (ds.Tables[0].Rows[0]["补助时间3"].ToString() != "")
                {
                    model.补助时间3 = DateTime.Parse(ds.Tables[0].Rows[0]["补助时间3"].ToString());
                }
                if (ds.Tables[0].Rows[0]["金额3"].ToString() != "")
                {
                    model.金额3 = int.Parse(ds.Tables[0].Rows[0]["金额3"].ToString());
                }
                if (ds.Tables[0].Rows[0]["补助时间4"].ToString() != "")
                {
                    model.补助时间4 = DateTime.Parse(ds.Tables[0].Rows[0]["补助时间4"].ToString());
                }
                if (ds.Tables[0].Rows[0]["金额4"].ToString() != "")
                {
                    model.金额4 = int.Parse(ds.Tables[0].Rows[0]["金额4"].ToString());
                }
                if (ds.Tables[0].Rows[0]["补助时间5"].ToString() != "")
                {
                    model.补助时间5 = DateTime.Parse(ds.Tables[0].Rows[0]["补助时间5"].ToString());
                }
                if (ds.Tables[0].Rows[0]["金额5"].ToString() != "")
                {
                    model.金额5 = int.Parse(ds.Tables[0].Rows[0]["金额5"].ToString());
                }
                model.备注 = ds.Tables[0].Rows[0]["备注"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select 单位,No,姓名,性别 as sex,CASE 性别 WHEN '0' THEN '男' WHEN '1' THEN '女'end as 性别,CONVERT(varchar(100),投保时间, 23) as 投保时间,投保金额,CONVERT(varchar(100),补助时间1, 23) as 补助时间1,金额1,CONVERT(varchar(100),补助时间2, 23) as 补助时间2,金额2,CONVERT(varchar(100),补助时间3, 23) as 补助时间3,金额3,CONVERT(varchar(100),补助时间4, 23) as 补助时间4,金额4,CONVERT(varchar(100),补助时间5, 23) as 补助时间5,金额5,备注,工会单位信息.单位名称");
            strSql.Append(" FROM 互助员工统计表 ");
            strSql.Append(" LEFT JOIN 工会单位信息 ON 工会单位信息.ID=互助员工统计表.单位");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" 单位,No,姓名,性别,投保时间,投保金额,补助时间1,金额1,补助时间2,金额2,补助时间3,金额3,补助时间4,金额4,补助时间5,金额5,备注 ");
            strSql.Append(" FROM 互助员工统计表 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }



        public bool UpdateList(List<GH.Model.互助员工统计表> listModel)
        {
            try
            {

                System.Collections.Generic.List<String> listStr = new System.Collections.Generic.List<string>();

                System.Text.StringBuilder strSqlDelete = new StringBuilder();




                foreach (GH.Model.互助员工统计表 model in listModel)
                {
                    if (model.No == 0)
                    {
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder strSql1 = new StringBuilder();
                        StringBuilder strSql2 = new StringBuilder();

                        strSql1.Append("单位,");
                        strSql2.Append("'" + model.单位 + "',");

                        if (model.姓名 != null)
                        {
                            strSql1.Append("姓名,");
                            strSql2.Append("'" + model.姓名 + "',");
                        }
                        if (model.性别 != null)
                        {
                            strSql1.Append("性别,");
                            strSql2.Append("'" + model.性别 + "',");
                        }
                        if (model.投保时间 != null)
                        {
                            strSql1.Append("投保时间,");
                            strSql2.Append("'" + model.投保时间 + "',");
                        }
                        if (model.投保金额 != null)
                        {
                            strSql1.Append("投保金额,");
                            strSql2.Append("" + model.投保金额 + ",");
                        }
                        if (model.补助时间1 != null)
                        {
                            strSql1.Append("补助时间1,");
                            strSql2.Append("'" + model.补助时间1 + "',");
                        }
                        if (model.金额1 != null)
                        {
                            strSql1.Append("金额1,");
                            strSql2.Append("" + model.金额1 + ",");
                        }
                        if (model.补助时间2 != null)
                        {
                            strSql1.Append("补助时间2,");
                            strSql2.Append("'" + model.补助时间2 + "',");
                        }
                        if (model.金额2 != null)
                        {
                            strSql1.Append("金额2,");
                            strSql2.Append("" + model.金额2 + ",");
                        }
                        if (model.补助时间3 != null)
                        {
                            strSql1.Append("补助时间3,");
                            strSql2.Append("'" + model.补助时间3 + "',");
                        }
                        if (model.金额3 != null)
                        {
                            strSql1.Append("金额3,");
                            strSql2.Append("" + model.金额3 + ",");
                        }
                        if (model.补助时间4 != null)
                        {
                            strSql1.Append("补助时间4,");
                            strSql2.Append("'" + model.补助时间4 + "',");
                        }
                        if (model.金额4 != null)
                        {
                            strSql1.Append("金额4,");
                            strSql2.Append("" + model.金额4 + ",");
                        }
                        if (model.补助时间5 != null)
                        {
                            strSql1.Append("补助时间5,");
                            strSql2.Append("'" + model.补助时间5 + "',");
                        }
                        if (model.金额5 != null)
                        {
                            strSql1.Append("金额5,");
                            strSql2.Append("" + model.金额5 + ",");
                        }
                        if (model.备注 != null)
                        {
                            strSql1.Append("备注,");
                            strSql2.Append("'" + model.备注 + "',");
                        }
                        strSql.Append("insert into 互助员工统计表(");
                        strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
                        strSql.Append(")");
                        strSql.Append(" values (");
                        strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
                        strSql.Append(")");

                        listStr.Add(strSql.ToString());
                    }
                    else
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update 互助员工统计表 set ");
                        if (model.姓名 != null)
                        {
                            strSql.Append("姓名='" + model.姓名 + "',");
                        }
                        if (model.性别 != null)
                        {
                            strSql.Append("性别='" + model.性别 + "',");
                        }
                        if (model.投保时间 != null)
                        {
                            strSql.Append("投保时间='" + model.投保时间 + "',");
                        }
                        if (model.投保金额 != null)
                        {
                            strSql.Append("投保金额=" + model.投保金额 + ",");
                        }
                        if (model.补助时间1 != null)
                        {
                            strSql.Append("补助时间1='" + model.补助时间1 + "',");
                        }
                        else
                        {
                            strSql.Append("补助时间1=null,");
                        }
                        if (model.金额1 != null)
                        {
                            strSql.Append("金额1=" + model.金额1 + ",");
                        }
                        else
                        {
                            strSql.Append("金额1=null,");
                        }
                        if (model.补助时间2 != null)
                        {
                            strSql.Append("补助时间2='" + model.补助时间2 + "',");
                        }
                        else
                        {
                            strSql.Append("补助时间2=null,");
                        }
                        if (model.金额2 != null)
                        {
                            strSql.Append("金额2=" + model.金额2 + ",");
                        }
                        else
                        {
                            strSql.Append("金额2=null,");
                        }
                        if (model.补助时间3 != null)
                        {
                            strSql.Append("补助时间3='" + model.补助时间3 + "',");
                        }
                        else
                        {
                            strSql.Append("补助时间3=null,");
                        }
                        if (model.金额3 != null)
                        {
                            strSql.Append("金额3=" + model.金额3 + ",");
                        }
                        else
                        {
                            strSql.Append("金额3=null,");
                        }
                        if (model.补助时间4 != null)
                        {
                            strSql.Append("补助时间4='" + model.补助时间4 + "',");
                        }
                        else
                        {
                            strSql.Append("补助时间4=null,");
                        }
                        if (model.金额4 != null)
                        {
                            strSql.Append("金额4=" + model.金额4 + ",");
                        }
                        else
                        {
                            strSql.Append("金额4=null,");
                        }
                        if (model.补助时间5 != null)
                        {
                            strSql.Append("补助时间5='" + model.补助时间5 + "',");
                        }
                        else
                        {
                            strSql.Append("补助时间5=null,");
                        }
                        if (model.金额5 != null)
                        {
                            strSql.Append("金额5=" + model.金额5 + ",");
                        }
                        else
                        {
                            strSql.Append("金额5=null,");
                        }
                        if (model.备注 != null)
                        {
                            strSql.Append("备注='" + model.备注 + "',");
                        }
                        int n = strSql.ToString().LastIndexOf(",");
                        strSql.Remove(n, 1);
                        strSql.Append(" where 单位=" + model.单位 + " and No=" + model.No + " ");
                        listStr.Add(strSql.ToString());
                    }

                }
                DbHelperSQL.ExecuteSqlTran(listStr);

            }
            catch
            {

                throw;
            }
            return true;
        }

        /*
        */

        #endregion  成员方法
    }
}

